from DAL.sqlHelper import (get_keyboard_conn as conn_a,SQL_ADD_FIELD_VALUES
,SQL_UPDATE_SET,SQL_ORDER,SQL_WHERE)
"""
组织管理模块，包括部门管理、设备管理、组员管理
"""

class departmentDAL:
    def getList(self, department="",group="",page=1, limit=20, filed='id', sort='ascending'):
        sql = """
               SELECT id,department,`group`
               FROM `org_department`       
               {0}
               {1}
              """
        sql_where = " WHERE department like '%{0}%' and  `group` like '%{1}%' ".format(department,group)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `org_department`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()


    def getListAll(self):
        sql = """
               SELECT DISTINCT department,`group`
               FROM org_department
              """
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  org_department
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,department=None,group=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `org_department`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self, department=None,group=None):
        param = locals()
        sql = """
               INSERT INTO  `org_department`({0})
               VALUES({1})
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `org_department`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param)
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()

class deviceDAL:
    def getList(self, name="",ip="",page=1, limit=20, filed='id', sort='ascending'):
        sql = """
               SELECT id,`name`,`ip`
               FROM `org_device`       
               {0}
               {1}
              """
        sql_where = " WHERE `name` like '%{0}%' and  `ip` like '%{1}%' ".format(name,ip)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `org_device`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()


    def getListAll(self):
        sql = """
                 SELECT DISTINCT `name`
                 FROM org_device
                 """
        # print(sql)
        with conn_a() as conn:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  org_device
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,name=None,ip=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `org_device`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self, name=None,ip=None):
        param = locals()
        sql = """
               INSERT INTO  `org_device`({0})
               VALUES({1})
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `org_device`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param)
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()


class teamMembersDAL:

    def getList(self, name="",department=None,group=None,jurisdiction=None,pid=None,page=1,limit=20,filed='id',sort='ascending'):
        param = locals()
        sql = """
               SELECT id,`name`,department,`group`,`jurisdiction`,`pid`,`equipment`,`bind_account`,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS create_time
               FROM `org_team_members`       
               {0}
               {1}
              """
        sql_where_1 = SQL_WHERE(locals_filed=param,only_contain_field=['department','group','jurisdiction','pid'])
        sql_where_1 = f' and {sql_where_1}' if sql_where_1 else ''
        sql_where = " WHERE name like '%{0}%'  {1}".format(name,sql_where_1)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `org_team_members`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                res = self.getListExp(cursor,res)
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def getListExp(self,cursor,list1):
        for it in list1:
            it['equipment'] = self.getDeviceById(cursor,it['id'])
        return list1
    def getDeviceById(self,cursor,member_id):
        sql_1 = """
            SELECT   device_name
            FROM     org_members_device_ref
            WHERE   member_id = '{0}'
                   """
        sql_1 = sql_1.format(member_id)
        cursor.execute(sql_1)
        res = cursor.fetchall()
        res = res or []
        res1=[]
        for it in res:
            res1.append(it['device_name'])
        return res1

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  org_team_members
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        sql_2 = """
                DELETE  org_members_device_ref
                FROM  org_members_device_ref
                WHERE  member_id='{0}'
                """
        sql_2 = sql_2.format(id)

        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_2)
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,name=None,department=None,group=None
               , jurisdiction=None,pid=None
               ,equipment=None
               ):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id','equipment'])
        sql = """
                 UPDATE 
                   `org_team_members`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                self.updateEquipment(id,equipment,cursor)
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount or 1
            return res
        finally:
            conn.close()

    def updateEquipment(self,id,equipment,cursor):
        sql_delete = """
                               DELETE  org_members_device_ref
                               FROM  org_members_device_ref
                               WHERE  member_id='{0}'
                           """
        sql_1 = sql_delete.format(id)
        cursor.execute(sql_1)
        if len(equipment) > 0:
            sql_values = []
            for it in equipment:
                sql_values.append("('{0}','{1}')".format(id, it))
            sql_values = ','.join(sql_values)
            sql_2 = """
                                   INSERT INTO  org_members_device_ref(`member_id`,`device_name`)
                                   VALUES {0}
                                                """
            sql_2 = sql_2.format(sql_values)
            cursor.execute(sql_2)



    def notExistAdd(self,id=None,name=None,department=None,group=None
               , jurisdiction=None,pid=None
               ,equipment=None):
        param = locals()
        sql = """
               INSERT INTO  `org_team_members`({0},create_time)
               VALUES({1},now())
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param,exclusion_field=['id','equipment'])
        sql_insert = sql.format(sql_field,sql_values)

        sql_unique = """
        SELECT id
        FROM `org_team_members`
        WHERE name='{0}'    
        """
        sql_1 = sql_unique.format(name)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone() or None
                # print(res_one)
                if not res_one:
                    cursor.execute(sql_insert)
                    conn.commit()

                    if (cursor.rowcount > 0):
                        cursor.execute(sql_1)
                        res_one = cursor.fetchone()
                        # print("信息", res_one)
                        id = res_one['id']

                        # print("出现id", id)

                        # 更新设备
                        self.updateEquipment(id, equipment, cursor)
                        conn.commit()
                    return 1

                else:
                    return 0
        finally:
            conn.close()


    def bindAccount(self, id=None,bind_account=""):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `org_team_members`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount or 1
                return res
        finally:
            conn.close()


    def getBoss(self):
        sql = """
               SELECT  `id`,`name` 
                FROM org_team_members
                WHERE jurisdiction in ('主管','组长')
              """
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()




if __name__=="__main__":
    dal = teamMembersDAL()
    # dal.notExistAdd("333")
    re = dal.getList()
    print(re)



